﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using BaseEntities;

namespace DataAccess
{
    public class DataAccess
    {
        public static bool ExeStoredValidCard(string StoredName, Card card)
        {
            SqlConnection conn = DBConnect.GetConnection();
            SqlCommand cmd = new SqlCommand();
            try
            {
                cmd.CommandText = StoredName;
                cmd.Connection = conn;
                conn.Open();
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@CardNo", SqlDbType.VarChar).Value = card.CardNo;
                SqlDataReader reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    conn.Close();
                    return true;
                }
                else
                {
                    conn.Close();
                    return false;
                }
            }
            catch (SqlException)
            {
                return false;
            }
        }

        public static bool ExeStoredValidPIN(string StoredName, Card card)
        {
            try
            {
                SqlConnection conn = DBConnect.GetConnection();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = StoredName;
                cmd.Connection = conn;
                conn.Open();
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@CardNo", SqlDbType.VarChar).Value = card.CardNo;
                cmd.Parameters.Add("@PIN", SqlDbType.VarChar).Value = card.PIN;
                SqlDataReader reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    conn.Close();
                    return true;
                }
                else
                {
                    conn.Close();
                    return false;
                }
            }
            catch (SqlException)
            {
                return false;
            }
        }

        public static bool ExeStoredUpdateStatus(string StoredName, Card card)
        {
            try
            {
                SqlConnection conn = DBConnect.GetConnection();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = StoredName;
                cmd.Connection = conn;
                conn.Open();
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@CardNo", SqlDbType.VarChar).Value = card.CardNo;

                int dem = cmd.ExecuteNonQuery();
                if (dem!=0)
                {
                    conn.Close();
                    return true;
                }
                else
                {
                    conn.Close();
                    return false;
                }
            }
            catch (SqlException)
            {
                return false;
            }
        }

        public static bool ExeStoredUpdateAttempt(string StoredName, Card card)
        {
            try
            {
                SqlConnection conn = DBConnect.GetConnection();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = StoredName;
                cmd.Connection = conn;
                conn.Open();
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("CardNo", SqlDbType.VarChar).Value = card.CardNo;

                int dem = cmd.ExecuteNonQuery();
                if (dem != 0)
                {
                    conn.Close();
                    return true;
                }
                else
                {
                    conn.Close();
                    return false;
                }
            }
            catch (SqlException)
            {
                return false;
            }
        }

        public static bool ExeStoredResetAttempt(string StoredName, Card card)
        {
            try
            {
                SqlConnection conn = DBConnect.GetConnection();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = StoredName;
                cmd.Connection = conn;
                conn.Open();
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@CardNo", SqlDbType.VarChar).Value = card.CardNo;

                int dem = cmd.ExecuteNonQuery();
                if (dem != 0)
                {
                    conn.Close();
                    return true;
                }
                else
                {
                    conn.Close();
                    return false;
                }
            }
            catch (SqlException)
            {
                return false;
            }
        }

        #region UC2_Withdraw
        public DataTable GetData(string query, params SqlParameter[] parameters)
        {
            SqlConnection conn = DBConnect.GetConnection();
            conn.Open();
            DataTable table = new DataTable();
            SqlCommand cmd = new SqlCommand(query, conn);
            cmd.Parameters.AddRange(parameters);
            table.Load(cmd.ExecuteReader());
            conn.Close();
            return table;
        }

        public void UpdateData(string query, params SqlParameter[] parameters)
        {
            SqlConnection conn = DBConnect.GetConnection();
            conn.Open();
            SqlCommand cmd = new SqlCommand(query, conn);
            cmd.Parameters.AddRange(parameters);
            cmd.ExecuteNonQuery();
            conn.Close();
        }

        public int GetAccountID(string query, params SqlParameter[] parameters)
        {
            SqlConnection conn = DBConnect.GetConnection();
            conn.Open();
            DataTable table = new DataTable();
            SqlCommand cmd = new SqlCommand(query, conn);
            cmd.Parameters.AddRange(parameters);
            SqlDataReader reader = cmd.ExecuteReader();
            reader.Read();
            Account acc = new Account();
            acc.AccountID = reader.GetInt32(0);
            conn.Close();
            return acc.AccountID;
        }
        public decimal GetBalance(string query, params SqlParameter[] parameters)
        {
            SqlConnection conn = DBConnect.GetConnection();
            conn.Open();
            SqlCommand cmd = new SqlCommand(query, conn);
            cmd.Parameters.AddRange(parameters);
            SqlDataReader reader = cmd.ExecuteReader();
            reader.Read();
            Account acc = new Account();
            acc.Balance = reader.GetDecimal(0);
            conn.Close();
            return acc.Balance;
        }

        public decimal GetMoneyInATM(string query, params SqlParameter[] parameters)
        {
            SqlConnection conn = DBConnect.GetConnection();
            conn.Open();
            SqlCommand cmd = new SqlCommand(query, conn);
            cmd.Parameters.AddRange(parameters);
            SqlDataReader reader = cmd.ExecuteReader();
            reader.Read();
            decimal sum = reader.GetDecimal(0);
            conn.Close();
            return sum;
        }

        public int GetQuantity(string query, params SqlParameter[] parameters)
        {
            SqlConnection conn = DBConnect.GetConnection();
            conn.Open();
            SqlCommand cmd = new SqlCommand(query, conn);
            cmd.Parameters.AddRange(parameters);
            SqlDataReader reader = cmd.ExecuteReader();
            reader.Read();
            Stock st = new Stock();
            st.Quantity = reader.GetInt32(0);
            conn.Close();
            return st.Quantity;
        }
        #endregion
    }
}
